package edu.uta.tsrh.db.commandpattern;

import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

import edu.uta.tsrh.db.DAO;
import edu.uta.tsrh.db.DBQueryExecuter;
import edu.uta.tsrh.db.QueryBuilder.SelectSQLBuilder;
import edu.uta.tsrh.db.mapper.Mapper;
import edu.uta.tsrh.db.mapper.PendingFilesMapper;
import edu.uta.tsrh.db.mapper.ReportPendingFilesMapper;
import edu.uta.tsrh.model.PendingFiles;
import edu.uta.tsrh.model.QueryCondition;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class GetReportPendingFiles
        implements MySQLCmd {

    private Timestamp startTime;
    private Timestamp endTime;

    public GetReportPendingFiles(String startTime, String endTime) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");
            java.util.Date date1 = sdf.parse(startTime);
            java.util.Date date2 = sdf.parse(endTime);
            java.sql.Timestamp timest1 = new java.sql.Timestamp(date1.getTime());
            java.sql.Timestamp timest2 = new java.sql.Timestamp(date2.getTime());
            this.startTime = timest1;
            this.endTime = timest2;
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    @Override
    public ArrayList<PendingFiles> execute() {
        String sqlQuery = buildQuery();
        System.out.println("query is " + sqlQuery);
        Connection connection = DAO.getConnection();

        DBQueryExecuter queryExecuter = new DBQueryExecuter();
        ResultSet resultSet = queryExecuter.read(sqlQuery, connection);

        Mapper mapper = new ReportPendingFilesMapper();
        ArrayList<PendingFiles> pendingFiles = (ArrayList<PendingFiles>) mapper.map(resultSet);

        DAO.closeConnection();
        return pendingFiles;
    }

    private String buildQuery() {
        String sqlQuery = null;
        SelectSQLBuilder selectSQLBuilder = new SelectSQLBuilder();
        selectSQLBuilder.addSelectColumn(" f.file_id AS file_id, p.firstname AS p_first, p.lastname AS p_last, f.file_state , pd.reason, pd.location, pd.time, u.firstName AS emp_first, u.lastName AS emp_last ");
        selectSQLBuilder.addFromTableName("physical_file f");
        selectSQLBuilder.addJoinTableName("transactions pd");
        selectSQLBuilder.addJoinCondition(new QueryCondition("f.file_id", "=", "pd.file_id"));
        selectSQLBuilder.addJoinTableName("patientrecord p");
        selectSQLBuilder.addJoinCondition(new QueryCondition("p.patientid", "=", "f.file_id"));
        selectSQLBuilder.addJoinTableName("user u");
        selectSQLBuilder.addJoinCondition(new QueryCondition("pd.employee_id", "=", "u.userId"));

        selectSQLBuilder.addWhereCondition(new QueryCondition("pd.time", ">=", this.startTime));
        selectSQLBuilder.addWhereCondition(new QueryCondition("pd.time", "<=", this.endTime));
        selectSQLBuilder.addGroupBy("f.file_id");

        selectSQLBuilder.construct();
        sqlQuery = selectSQLBuilder.getSQLString();
        return sqlQuery;
    }

    @Override
    public boolean isReversible() {
        return false;
    }

    @Override
    public void undo() {
    }
}
